package com.um.psystem.service.sopService.impl;

import cn.hutool.core.util.StrUtil;
import com.um.psystem.mapper.platform.PublicMapper;
import com.um.psystem.model.vo.JsonResult;
import com.um.psystem.service.sopService.ISOPUpdateService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Auther: QY
 * @Date: 2021/09/06 - 20:51
 * @Description: com.um.psystem.service.sopService.impl
 * @version: 1.0
 */
@Service
public class SOPUpdateService implements ISOPUpdateService {
    @Autowired
    PublicMapper publicMapper;

    @Override
    public List<Map<String, Object>> getSopRecordList(Map map) {

        String sqltmp = "";
        String bomMoudleNum = "";
        String operator = "";
        String currentuser = "";
        Map paramsMap = new HashMap();
        Integer page = Integer.parseInt((map.get("page") != null ? map.get("page").toString() : "0"));
        Integer rows = Integer.parseInt((map.get("rows") != null ? map.get("rows").toString() : "0"));

        if (StrUtil.isNotBlank(map.get("bomMoudleNum") != null ? map.get("bomMoudleNum").toString() : null)) {
            sqltmp += " and soplist.bom_module_num like #{bomMoudleNum}";
            bomMoudleNum = map.get("bomMoudleNum").toString();
            paramsMap.put("bomMoudleNum", "%"+bomMoudleNum+"%");
        }
        if (StrUtil.isNotBlank(map.get("operator") != null ? map.get("operator").toString() : null)) {
            sqltmp += " and soplist.operator=#{operator}";
            operator = map.get("operator").toString();
            paramsMap.put("operator", operator);
        }

        String sql = "select count(*) as totals " +
                "from (SELECT a.id,a.bom_module_num,a.bom_desc,c.production_process,a.download_url,a.version,a.controlled_num,a.date,a.operator,a.remark,b.name from ws_eng_sop_bom_module_dtl a join ws_eng_sop_bom_module_main b on a.main_id=b.id join ws_eng_sop_production_process c on a.production_process_id=c.id " +
                "UNION ALL " +
                "SELECT dtl.id,dtl.machine_model,NULL,process.production_process,dtl.download_url,dtl.version,dtl.controlled_num,dtl.date,dtl.operator,dtl.remark,'机型' FROM ws_eng_sop_machine_model_dtl dtl JOIN ws_eng_sop_production_process process ON dtl.production_process_id=process.id) soplist";
        paramsMap.put("sqlStr", sql);
        int totals = 0;
        List<Map<String, Object>> list_count = publicMapper.getPublicItems(paramsMap);
        if (list_count.size() > 0) {
            totals = Integer.parseInt(list_count.get(0).get("totals").toString());
        } else {
            System.out.println("****************:" + totals);
        }

        sql = "SELECT soplist.*,"+totals+" as totals FROM " +
                "(SELECT a.id,a.bom_module_num,a.bom_desc,c.production_process,a.download_url,a.version,a.controlled_num,a.date,a.operator,a.remark,b.name from ws_eng_sop_bom_module_dtl a LEFT JOIN ws_eng_sop_bom_module_main b on a.main_id=b.id join ws_eng_sop_production_process c on a.production_process_id=c.id " +
                "UNION ALL " +
                "SELECT dtl.id,dtl.machine_model,NULL,process.production_process,dtl.download_url,dtl.version,dtl.controlled_num,dtl.date,dtl.operator,dtl.remark,'机型' FROM ws_eng_sop_machine_model_dtl dtl JOIN ws_eng_sop_production_process process ON dtl.production_process_id=process.id) soplist " +
                "where 1=1 " + sqltmp + " order by soplist.id desc";

        if (page != 0 && rows != 0) {
            sql += " limit #{startIndex},#{rows} ";
            paramsMap.put("startIndex", (page - 1) * rows);
            paramsMap.put("rows", rows);
        }
        paramsMap.put("sqlStr", sql);
        return publicMapper.getPublicItems(paramsMap);
    }

    @Override
    public JsonResult<Integer> checkFileName(String[] fileNameAra) {
        Map paramsMap = new HashMap();
        String sql = "";
        List<Map<String, Object>> list_count = null;
        if(!fileNameAra[1].equals("通用SOP")){
            //检查编码是否与K3表中对应
            sql = "select FNumberShort as bom_module_num,FModel as bom_desc from " +
                    "ws_k3_icitem where FNumberShort=#{bom_module_num}";
            paramsMap.put("bom_module_num", fileNameAra[0]);
            paramsMap.put("sqlStr", sql);
            list_count = publicMapper.getPublicItems(paramsMap);
            System.out.println("list_count.size():"+list_count.size());
            if(list_count.size()==0){
                System.out.println("编码有问题——————————————————————————");
                return new JsonResult<>(400,"编码有问题");
            }
        }

        sql = "select * from  ws_eng_sop_production_process where production_process=#{production_process}";
        paramsMap.put("production_process",fileNameAra[1]);
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_production_process = publicMapper.getPublicItems(paramsMap);

        sql = "select * from ws_eng_sop_bom_module_dtl where version=#{version} and bom_module_num=#{bom_module_num}";
        paramsMap.put("version",fileNameAra[2]);
        paramsMap.put("bom_module_num",fileNameAra[0]);
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_dtl = publicMapper.getPublicItems(paramsMap);

        sql = "SELECT * FROM ws_eng_sop_bom_module_dtl WHERE bom_module_num <>#{bom_module_num} AND controlled_num=#{controlled_num}";
        paramsMap.put("controlled_num",fileNameAra[3]);
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_dtl2 = publicMapper.getPublicItems(paramsMap);

        sql = "SELECT controlled_num FROM ws_eng_sop_machine_model_dtl WHERE controlled_num=#{controlled_num}";
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_dtl3 = publicMapper.getPublicItems(paramsMap);

        if(list_production_process.size()==0){
            System.out.println("工段命名有问题——————————————————————————");
            return new JsonResult<>(400,"工段命名有问题");
        }else if(list_dtl.size()>0){
            System.out.println("版本号重复——————————————————————————");
            return new JsonResult<>(400,"版本号重复");
        }else if(list_dtl2.size()>0 || list_dtl3.size()>0){
            System.out.println("受控编号重复——————————————————————————");
            return new JsonResult<>(400,"受控编号重复");
        }else {
            return JsonResult.success();
        }
    }

    @Override
    public JsonResult<Integer> checkModelSopFileName(String[] fileNameAra) {
        Map paramsMap = new HashMap();
        String sql = "";
        List<Map<String, Object>> list_count = null;
        if(!fileNameAra[1].equals("通用SOP")){
            //检查编码是否与K3表中对应
            sql = "SELECT * FROM uws_produceorder_confirmation_query WHERE conProductNum=#{conProductNum}";
            paramsMap.put("conProductNum", fileNameAra[0]);
            paramsMap.put("sqlStr", sql);
            list_count = publicMapper.getPublicItems(paramsMap);
            System.out.println("list_count.size():"+list_count.size());
            if(list_count.size()==0){
                System.out.println("编码有问题——————————————————————————");
                return new JsonResult<>(400,"编码有问题");
            }
        }

        sql = "select * from ws_eng_sop_production_process where production_process=#{production_process}";
        paramsMap.put("production_process",fileNameAra[1]);
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_production_process = publicMapper.getPublicItems(paramsMap);

        if(list_production_process.size()==0){
            System.out.println("工段命名有问题——————————————————————————");
            return new JsonResult<>(400,"工段命名有问题");
        }

        sql = "SELECT * FROM ws_eng_sop_machine_model_dtl a WHERE a.machine_model =#{machine_model} AND a.production_process_id=#{production_process} AND a.version=#{version}";
        paramsMap.put("version",fileNameAra[2]);
        paramsMap.put("machine_model",fileNameAra[0]);
        paramsMap.put("production_process",list_production_process.get(0).get("id"));
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_dtl = publicMapper.getPublicItems(paramsMap);

        sql = "SELECT * FROM ws_eng_sop_machine_model_dtl " +
                "WHERE id NOT IN (SELECT id FROM ws_eng_sop_machine_model_dtl WHERE machine_model=#{machine_model} AND production_process_id = #{production_process}) " +
                "AND controlled_num=#{controlled_num}";
        paramsMap.put("controlled_num",fileNameAra[3]);
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_dtl2 = publicMapper.getPublicItems(paramsMap);

        sql = "SELECT controlled_num FROM ws_eng_sop_bom_module_dtl WHERE controlled_num=#{controlled_num}";
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_dtl3 = publicMapper.getPublicItems(paramsMap);

         if(list_dtl.size()>0){
            System.out.println("版本号重复——————————————————————————");
            return new JsonResult<>(400,"版本号重复");
        }else if(list_dtl2.size()>0||list_dtl3.size()>0){
            System.out.println("受控编号重复——————————————————————————");
            return new JsonResult<>(400,"受控编号重复");
        }else {
            return JsonResult.success();
        }
    }

    @Override
    public JsonResult<Integer> deleSOPRecord(Integer id,String url) {
        //删除文件
        String path = "/usr/local/psystemfile/SOP" + url;
        File file = new File(path);
        if(file.isFile()){
            file.delete();
        }

        Map paramsMap = new HashMap();
        String sql = "DELETE FROM ws_eng_sop_bom_module_dtl" +
                " WHERE" +
                " id=#{id}";
        paramsMap.put("dsqlStr",sql);
        paramsMap.put("id",id);
        Integer updateNum = publicMapper.delItems(paramsMap);
        return JsonResult.success(updateNum);
    }

    @Override
    public JsonResult<Integer> deleMachineTypeSOPRecord(Integer id, String url) {
        //删除文件
        String path = "/usr/local/psystemfile/ModelSOP" + url;
        File file = new File(path);
        if(file.isFile()){
            file.delete();
        }

        Map paramsMap = new HashMap();
        String sql = "DELETE FROM ws_eng_sop_machine_model_dtl" +
                " WHERE" +
                " id=#{id}";
        paramsMap.put("dsqlStr",sql);
        paramsMap.put("id",id);
        Integer updateNum = publicMapper.delItems(paramsMap);
        return JsonResult.success(updateNum);
    }


    @Override
    public JsonResult<Integer> addSOPRecordByCurrent(Map map, String[] fileNameAra, String dir1, String url) {
        Map paramsMap = new HashMap();
        String sql = "select * from  ws_eng_sop_production_process where production_process=#{production_process}";
        paramsMap.put("production_process",fileNameAra[1]);
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_production_process = publicMapper.getPublicItems(paramsMap);
        Integer production_process_id = (Integer) list_production_process.get(0).get("id");

        sql = "INSERT INTO ws_eng_sop_bom_module_dtl" +
                " VALUES" +
                " (NULL, #{bom_module_num}, #{main_id}, #{bom_desc}, #{production_process_id}," +
                "#{version}, #{controlled_num}, now(),#{operator}, #{download_url}, ''," +
                "'','0')";
        paramsMap.put("isqlStr",sql);
        paramsMap.put("bom_module_num",fileNameAra[0]);
        paramsMap.put("main_id",null);
        paramsMap.put("bom_desc","");
        paramsMap.put("production_process_id",production_process_id);
        paramsMap.put("version",fileNameAra[2]);
        paramsMap.put("controlled_num",fileNameAra[3]);
        paramsMap.put("operator",map.get("username"));
        paramsMap.put("download_url",url);
        Integer updateNum = publicMapper.saveItems(paramsMap);
        return JsonResult.success(updateNum);
    }

    @Override
    public JsonResult<Integer> addSOPRecordByMachineType(Map map, String[] fileNameAra, String url) {
        Map paramsMap = new HashMap();
        String sql = "INSERT INTO ws_eng_sop_machine_model_dtl " +
                "SELECT NULL,#{machine_model},b.id,#{version},#{controlled_num},NOW(),#{operator},#{download_url},#{record},#{remark},#{check_flag} " +
                "FROM ws_eng_sop_production_process b WHERE b.production_process=#{production_process}";
        paramsMap.put("isqlStr",sql);
        paramsMap.put("machine_model",fileNameAra[0]);
        paramsMap.put("production_process",fileNameAra[1]);
        paramsMap.put("version",fileNameAra[2]);
        paramsMap.put("controlled_num",fileNameAra[3]);
        paramsMap.put("operator",map.get("username"));
        paramsMap.put("download_url",url);
        paramsMap.put("record",map.get("record"));
        paramsMap.put("remark",map.get("remark"));
        paramsMap.put("check_flag",map.get("check_flag"));
        Integer updateNum = publicMapper.saveItems(paramsMap);
        return JsonResult.success(updateNum);
    }

    @Override
    public JsonResult<Integer> addSOPRecord(Map map,String[] fileNameAra,String dir1,String url) {
        Map paramsMap = new HashMap();
        String sql = "select FNumberShort as bom_module_num,FModel as bom_desc from " +
                "ws_k3_icitem where FNumberShort=#{bom_module_num}";
        paramsMap.put("bom_module_num",fileNameAra[0]);
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_count = publicMapper.getPublicItems(paramsMap);
        String bom_desc = (String) list_count.get(0).get("bom_desc");

        sql = "select * from ws_eng_sop_bom_module_main where bom_prefix=#{bom_prefix}";
        paramsMap.put("bom_prefix",dir1);
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_mainID = publicMapper.getPublicItems(paramsMap);
        String main_id = list_mainID.get(0).get("id").toString();

        sql = "select * from  ws_eng_sop_production_process where production_process=#{production_process}";
        paramsMap.put("production_process",fileNameAra[1]);
        paramsMap.put("sqlStr", sql);
        List<Map<String, Object>> list_production_process = publicMapper.getPublicItems(paramsMap);
        String production_process_id = list_production_process.get(0).get("id").toString();

        sql = "INSERT INTO ws_eng_sop_bom_module_dtl" +
                " VALUES" +
                " (NULL, #{bom_module_num}, #{main_id}, #{bom_desc}, #{production_process_id}," +
                "#{version}, #{controlled_num}, now(),#{operator}, #{download_url}, ''," +
                "'','0')";
        paramsMap.put("isqlStr",sql);
        paramsMap.put("bom_module_num",fileNameAra[0]);
        paramsMap.put("main_id",main_id);
        paramsMap.put("bom_desc",bom_desc);
        paramsMap.put("production_process_id",production_process_id);
        paramsMap.put("version",fileNameAra[2]);
        paramsMap.put("controlled_num",fileNameAra[3]);
        paramsMap.put("operator",map.get("username"));
        paramsMap.put("download_url",url);
        Integer updateNum = publicMapper.saveItems(paramsMap);
        return JsonResult.success(updateNum);
    }
}
